﻿USE [pulog]
GO

/****** Object:  Table [dbo].[User]    Script Date: 10/01/2010 22:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
	[userID] [int] IDENTITY(1,1) NOT NULL,
	[userName] [nvarchar](50) NULL,
	[password] [nvarchar](50) NULL,
	[regTime] [datetime] NULL,
	[email] [nvarchar](50) NULL,
	[homepage] [nvarchar](100) NULL,
	[QQ] [int] NULL,
	[msn] [nvarchar](50) NULL,
	[info] [text] NULL,
	[gender] [int] NULL,
	[location] [nvarchar](50) NULL,
	[regIP] [nvarchar](20) NULL,
	[isAdmin] [bit] NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[userID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[User] ON
-- admin  admin
INSERT [dbo].[User] ([userID], [userName], [password], [regTime], [email], [homepage], [QQ], [msn], [info], [gender], [location], [regIP], [isAdmin]) VALUES (1, N'admin', N'0f50302b276b88ee7db0cc597047d13b', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1)
SET IDENTITY_INSERT [dbo].[User] OFF
/****** Object:  Table [dbo].[Tags]    Script Date: 10/01/2010 22:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tags](
	[TagID] [int] IDENTITY(1,1) NOT NULL,
	[TagName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED 
(
	[TagID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Settings]    Script Date: 10/01/2010 22:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Settings](
	[Key] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Value] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED 
(
	[Key] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Settings] ([Key], [Name], [Value]) VALUES (N'AttachmentPath', N'Attachment Path', N'')
INSERT [dbo].[Settings] ([Key], [Name], [Value]) VALUES (N'BlogDescription', N'Blog Description', N'Shawn''s Blog...')
INSERT [dbo].[Settings] ([Key], [Name], [Value]) VALUES (N'BlogName', N'Blog Name', N'NZ Living...')
INSERT [dbo].[Settings] ([Key], [Name], [Value]) VALUES (N'BlogURL', N'Blog URL', N'http://www.shawnjiang.com')
INSERT [dbo].[Settings] ([Key], [Name], [Value]) VALUES (N'MaxCommentLevel', N'Max Comment Reply Level', N'6')
/****** Object:  Table [dbo].[Links]    Script Date: 10/01/2010 22:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Links](
	[linkID] [int] IDENTITY(1,1) NOT NULL,
	[linkName] [nvarchar](50) NOT NULL,
	[linkDescription] [nvarchar](max) NULL,
	[linkURL] [nvarchar](100) NOT NULL,
	[linkOrder] [int] NULL,
 CONSTRAINT [PK_Links] PRIMARY KEY CLUSTERED 
(
	[linkID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Links] ON
INSERT [dbo].[Links] ([linkID], [linkName], [linkDescription], [linkURL], [linkOrder]) VALUES (1, N'Shawn', N'Pulog的作者shawn的部落格', N'http://shawnjiang.com', 1)
SET IDENTITY_INSERT [dbo].[Links] OFF
/****** Object:  Table [dbo].[Category]    Script Date: 10/01/2010 22:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
	[categoryID] [int] IDENTITY(1,1) NOT NULL,
	[categoryName] [nvarchar](50) NULL,
	[categoryDesc] [ntext] NULL,
	[categoryOrder] [int] NULL,
	[categoryIcon] [nvarchar](100) NULL,
	[categoryURL] [nvarchar](100) NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[categoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Category] ON
INSERT [dbo].[Category] ([categoryID], [categoryName], [categoryDesc], [categoryOrder], [categoryIcon], [categoryURL]) VALUES (1, N'生活杂记', N'生活琐事杂记以及一些其他未能归类的东东', 1, NULL, NULL)
INSERT [dbo].[Category] ([categoryID], [categoryName], [categoryDesc], [categoryOrder], [categoryIcon], [categoryURL]) VALUES (2, N'转载文章', N'转来的好东东。。。', 2, NULL, NULL)
SET IDENTITY_INSERT [dbo].[Category] OFF
/****** Object:  Table [dbo].[Post]    Script Date: 10/01/2010 22:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Post](
	[postID] [int] IDENTITY(1,1) NOT NULL,
	[title] [ntext] NOT NULL,
	[pubTime] [datetime] NOT NULL,
	[authorID] [int] NOT NULL,
	[content] [ntext] NOT NULL,
	[categoryID] [int] NOT NULL,
	[views] [int] NOT NULL,
	[sticky] [int] NOT NULL,
	[slug] [nvarchar](100) NOT NULL,
	[password] [nvarchar](50) NOT NULL,
	[property] [int] NOT NULL,
 CONSTRAINT [PK_blogs] PRIMARY KEY CLUSTERED 
(
	[postID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Post] ON
INSERT [dbo].[Post] ([postID], [title], [pubTime], [authorID], [content], [categoryID], [views], [sticky], [slug], [password], [property]) VALUES (1, N'这是一篇测试日志', GETDATE(), 1, N'欢迎使用Pulog，这是一篇测试日志，请删除。 ', 1, 0, 0, N'测试日志', N'0', 0)
SET IDENTITY_INSERT [dbo].[Post] OFF
/****** Object:  Table [dbo].[Attachment]    Script Date: 10/01/2010 22:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Attachment](
	[attachmentID] [int] IDENTITY(1,1) NOT NULL,
	[filePath] [nvarchar](500) NOT NULL,
	[originalName] [nvarchar](200) NOT NULL,
	[downloadCount] [int] NOT NULL,
	[uploadTime] [int] NOT NULL,
	[uploadUserID] [int] NOT NULL,
 CONSTRAINT [PK_Attachment] PRIMARY KEY CLUSTERED 
(
	[attachmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PostTag]    Script Date: 10/01/2010 22:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PostTag](
	[PostTagID] [int] IDENTITY(1,1) NOT NULL,
	[PostID] [int] NOT NULL,
	[TagID] [int] NOT NULL,
 CONSTRAINT [PK_PostTag] PRIMARY KEY CLUSTERED 
(
	[PostTagID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Comment]    Script Date: 10/01/2010 22:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Comment](
	[CommentID] [int] IDENTITY(1,1) NOT NULL,
	[CommentProperty] [int] NULL,
	[PostID] [int] NOT NULL,
	[CommentTime] [datetime] NOT NULL,
	[AuthorID] [int] NULL,
	[AuthorName] [nvarchar](50) NOT NULL,
	[AuthorEmail] [nvarchar](100) NULL,
	[AuthorURL] [nvarchar](100) NULL,
	[AuthorIP] [nvarchar](30) NULL,
	[CommentContent] [ntext] NOT NULL,
	[CommentPassword] [nvarchar](20) NULL,
	[IsApproved] [bit] NULL,
	[CommentParentID] [int] NULL,
 CONSTRAINT [PK_Comment] PRIMARY KEY CLUSTERED 
(
	[CommentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 - public 
1 - hidden
2 - waiting for pass public
3 - waiting for pass hidden' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment', @level2type=N'COLUMN',@level2name=N'CommentProperty'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'-1 - guest' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment', @level2type=N'COLUMN',@level2name=N'AuthorID'
GO
SET IDENTITY_INSERT [dbo].[Comment] ON
INSERT [dbo].[Comment] ([CommentID], [CommentProperty], [PostID], [CommentTime], [AuthorID], [AuthorName], [AuthorEmail], [AuthorURL], [AuthorIP], [CommentContent], [CommentPassword], [IsApproved], [CommentParentID]) VALUES (1, 0, 1, GetDate(), NULL, N'guest', N'', N'', N'10.1.1.1', N'测试评论', NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[Comment] OFF
/****** Object:  Default [DF_Pulog_isAdmin]    Script Date: 10/01/2010 22:51:05 ******/
ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_Pulog_isAdmin]  DEFAULT ((0)) FOR [isAdmin]
GO
/****** Object:  ForeignKey [FK_blogs_Category]    Script Date: 10/01/2010 22:51:05 ******/
ALTER TABLE [dbo].[Post]  WITH NOCHECK ADD  CONSTRAINT [FK_blogs_Category] FOREIGN KEY([categoryID])
REFERENCES [dbo].[Category] ([categoryID])
GO
ALTER TABLE [dbo].[Post] CHECK CONSTRAINT [FK_blogs_Category]
GO
/****** Object:  ForeignKey [FK_blogs_User]    Script Date: 10/01/2010 22:51:05 ******/
ALTER TABLE [dbo].[Post]  WITH NOCHECK ADD  CONSTRAINT [FK_blogs_User] FOREIGN KEY([authorID])
REFERENCES [dbo].[User] ([userID])
GO
ALTER TABLE [dbo].[Post] CHECK CONSTRAINT [FK_blogs_User]
GO
/****** Object:  ForeignKey [FK_Attachment_User]    Script Date: 10/01/2010 22:51:05 ******/
ALTER TABLE [dbo].[Attachment]  WITH NOCHECK ADD  CONSTRAINT [FK_Attachment_User] FOREIGN KEY([uploadUserID])
REFERENCES [dbo].[User] ([userID])
GO
ALTER TABLE [dbo].[Attachment] CHECK CONSTRAINT [FK_Attachment_User]
GO
/****** Object:  ForeignKey [FK_PostTag_Post]    Script Date: 10/01/2010 22:51:05 ******/
ALTER TABLE [dbo].[PostTag]  WITH CHECK ADD  CONSTRAINT [FK_PostTag_Post] FOREIGN KEY([PostID])
REFERENCES [dbo].[Post] ([postID])
GO
ALTER TABLE [dbo].[PostTag] CHECK CONSTRAINT [FK_PostTag_Post]
GO
/****** Object:  ForeignKey [FK_PostTag_Tags]    Script Date: 10/01/2010 22:51:05 ******/
ALTER TABLE [dbo].[PostTag]  WITH CHECK ADD  CONSTRAINT [FK_PostTag_Tags] FOREIGN KEY([TagID])
REFERENCES [dbo].[Tags] ([TagID])
GO
ALTER TABLE [dbo].[PostTag] CHECK CONSTRAINT [FK_PostTag_Tags]
GO
/****** Object:  ForeignKey [FK_Comment_Comment]    Script Date: 10/01/2010 22:51:05 ******/
ALTER TABLE [dbo].[Comment]  WITH NOCHECK ADD  CONSTRAINT [FK_Comment_Comment] FOREIGN KEY([CommentParentID])
REFERENCES [dbo].[Comment] ([CommentID])
GO
ALTER TABLE [dbo].[Comment] CHECK CONSTRAINT [FK_Comment_Comment]
GO
/****** Object:  ForeignKey [FK_Comment_Post]    Script Date: 10/01/2010 22:51:05 ******/
ALTER TABLE [dbo].[Comment]  WITH NOCHECK ADD  CONSTRAINT [FK_Comment_Post] FOREIGN KEY([PostID])
REFERENCES [dbo].[Post] ([postID])
GO
ALTER TABLE [dbo].[Comment] CHECK CONSTRAINT [FK_Comment_Post]
GO





/* change Post.pubTime into type int */
/* transfer data */
/* run following 
	update Post
	set tt = dateadd(ss,pubTime,'01/01/1970') */

/* change Post.pubTime into type Nvarchar 
	ALTER TABLE dbo.Post ALTER COLUMN pubTime Nvarchar(50)
*/
/* run following */
/*
	update Post
	set pubtime = '2009-1-1'
*/

/* change Post.pubTime into type DateTime 
	ALTER TABLE dbo.Post ALTER COLUMN pubTime datetime
*/
/* run following */
/*update Post
set pubTime = tt*/

/* delete column tt */

/* run following */
/*
update dbo.Post
set PostID = PostID + 1*/


/* run following 
		update Comment
		set tt = dateadd(ss,CommentTime,'01/01/1970')
   change Comment.CommentTime to type nvarchar
		ALTER TABLE dbo.Comment ALTER COLUMN CommentTime nvarchar(50)
   run following
		update Comment
		set CommentTime = '2009-1-1'
   change Post.pubTime into type DateTime 
		ALTER TABLE dbo.Comment ALTER COLUMN CommentTime datetime
   run following
		update Comment
		set CommentTime = tt
*/
	





/* convert ID column back to identity */
